CREATE TABLE CacheTest (
	Context nvarchar(128) NOT NULL,
	[Key] nvarchar(128) NOT NULL,
	Item image NOT NULL,
	AbsoluteExpiration datetime NOT NULL,
	SlidingExpiration int NOT NULL
)
GO

ALTER TABLE CacheTest ADD CONSTRAINT UC_CacheTest UNIQUE (Context, [Key])
GO

CREATE CLUSTERED INDEX IDX_CacheTest ON CacheTest (Context)
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE CacheTestInsert (
	@Context nvarchar(128),
	@Key nvarchar(128),
	@Item image,
	@AbsoluteExpiration datetime,
	@SlidingExpiration int
)

AS

INSERT INTO CacheTest VALUES (
	@Context,
	@Key,
	@Item,
	@AbsoluteExpiration,
	@SlidingExpiration
)
GO

GRANT EXECUTE ON dbo.CacheTestInsert TO SharpCoreUser
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE CacheTestUpdate (
	@Context nvarchar(128),
	@Key nvarchar(128),
	@Item image
)

AS

DECLARE @SlidingExpiration int
DECLARE @AbsoluteExpiration datetime

SET @AbsoluteExpiration = (SELECT AbsoluteExpiration FROM CacheTest WHERE Context = @Context AND [Key] = @Key)
SET @SlidingExpiration = (SELECT SlidingExpiration FROM CacheTest WHERE Context = @Context AND [Key] = @Key)

IF (@SlidingExpiration <> 0) BEGIN
	SET @AbsoluteExpiration = DATEADD(second, @SlidingExpiration, @AbsoluteExpiration)
END

UPDATE CacheTest
SET
	Item = @Item,
	AbsoluteExpiration = @AbsoluteExpiration
WHERE
	Context = @Context
	AND [Key] = @Key
GO

GRANT EXECUTE ON dbo.CacheTestUpdate TO SharpCoreUser
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE CacheTestDelete (
	@Context nvarchar(128),
	@Key nvarchar(128)
)

AS

DELETE FROM CacheTest
WHERE
	Context = @Context
	AND [Key] = @Key
GO

GRANT EXECUTE ON dbo.CacheTestDelete TO SharpCoreUser
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE CacheTestDeleteAll

AS

DELETE FROM CacheTest
GO

GRANT EXECUTE ON dbo.CacheTestDeleteAll TO SharpCoreUser
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE CacheTestPurge (
	@Context nvarchar(128)
)

AS

DELETE FROM CacheTest
WHERE
	Context = @Context
	AND DATEDIFF(second, AbsoluteExpiration, GETDATE()) <= 0
GO

GRANT EXECUTE ON dbo.CacheTestPurge TO SharpCoreUser
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE CacheTestSelect (
	@Context nvarchar(128),
	@Key nvarchar(128)
)

AS

SELECT
	Context,
	[Key],
	Item,
	AbsoluteExpiration,
	SlidingExpiration
FROM
	CacheTest
WHERE
	Context = @Context
	AND [Key] = @Key
GO

GRANT EXECUTE ON dbo.CacheTestSelect TO SharpCoreUser
GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE dbo.CacheTestSelectAll (
	@Context nvarchar(128)
)

AS

SELECT
	Context,
	[Key],
	Item,
	AbsoluteExpiration,
	SlidingExpiration
FROM
	CacheTest
WHERE
	Context = @Context
GO

GRANT EXECUTE ON dbo.CacheTestSelectAll TO SharpCoreUser
GO
